#!/usr/bin/python
# -*- coding: utf-8 -*-
import sys
from PyQt4 import QtCore, QtGui
#import mysql_core
import mysql_mod
from mysql_windows import Ui_mysql_Window
# qtCreatorFile = "mysql_windows.ui"  # Enter file here.
#
# Ui_MainWindow, QtBaseClass = uic.loadUiType(qtCreatorFile)
#
# default_encoding = "utf-8"
# if (default_encoding != sys.getdefaultendoding()):
#     reload(sys)
#     sys.setdefaultencoding(default_encoding)
#
#
# class mysql_tool(QtGui.QMainWindow, Ui_MainWindow):
#     def __init__(self):
#         QtGui.QMainWindow.__init__(self)
#         Ui_MainWindow.__init__(self)
#         self.setupUi(self)

class mysql_tool(QtGui.QMainWindow, Ui_mysql_Window):
    """b
    #方法二：先把ui文件转换成py文件。
    再通过继承 ui中的类Ui_MainWindow，直接初始化
    """
    css = """
            *{{
                font-size: 20px;
                color: #000000;
                font-family: "Microsoft YaHei";
            }}
        """
    def __init__(self):
        super(mysql_tool, self).__init__()
        self.setupUi(self)
        self.__get_mysql_set()
        self.__bunttn_init()
        self.__init_style()
        self.file_name = ""

    def __init_style(self):
        file = QtCore.QFile('style.qss')
        file.open(QtCore.QFile.ReadOnly)
        styleSheet = file.readAll()
        styleSheet = unicode(styleSheet, encoding='utf8')
        QtGui.qApp.setStyleSheet(styleSheet)
        # self.setStyleSheet(self.css.format())
        #
        # fontId = QtGui.QFontDatabase.addApplicationFont("./Font/NotoSansHans-Regular.otf")
        # fontName = QtGui.QFontDatabase.applicationFontFamilies(fontId)[0]
        # self.font = QtGui.QFont(fontName, 100)
        # #self.font.setPointSize(32)
        # #self.tabWidget.setFont(self.font)

    def __bunttn_init(self):
        # 初始化按钮
        # 点击按钮触发 调用事件
        #self.button_mysql_open.clicked.connect(self.__open_file)
        self.button_mysql_save.clicked.connect(self.__get_mysql_set)
        self.button_mysql_test.clicked.connect(self.__mysql_conn_test)
        self.button_mysql_connect.clicked.connect(self.__mysql_connectAnddisconnect)
        self.button_mysql_output.clicked.connect(self.__mysql_dump)
        self.button_mysql_exec.clicked.connect(self.__mysql_exec)
        #self.tab_result.setDisabled(True)
        self.tabWidget.removeTab(0)
        ##
    def __Changed_connect(self):
        # 选择数据库(comboBox)触发 调用事件
        self.comboBox_database.currentIndexChanged.connect(self.__mysql_table)
        # self.comboBox_database_io.currentIndexChanged.connect(self.__mysql_table_io)
        # 选择数据库表（listWidget）触发 调用事件
        self.listWidget_tables.currentRowChanged.connect(self.__mysql_value)

    def __mysql_conn_test(self):
        self.mysql_manager = mysql_mod.MYSQL_Manager(**self.mysql_config)
        if self.mysql_manager.isconnect :
            self.message("ok", "connect %s@%s ok" % (self.mysql_config['user'], self.mysql_config['host']))
            self.mysql_manager.close()
        else:
            self.message("sqlError",self.mysql_manager._dbError)

    def __mysql_connectAnddisconnect(self):
        self.comboBox_database.clear()
        self.comboBox_database.addItem("")
        # self.comboBox_database_io.clear()
        # self.comboBox_database_io.addItem("")

        if str(self.button_mysql_connect.text()) == "连接":
            self.__mysql_db()
            self.button_mysql_exec.setEnabled(True)
        else:
            try:
                self.mysql_show.close()
                self.message("ok", "disconnet ok!")
                self.button_mysql_connect.setText(u"连接")
                self.button_mysql_exec.setEnabled(False)
                self.button_mysql_open.setEnabled(False)
                self.button_mysql_input.setEnabled(False)
                self.button_mysql_output.setEnabled(False)
            except:
                self.message("error", "mysql is not connet")

    def __mysql_db(self):
        # _mysql = mysql_core.mysql_exec(self.mysql)
        # _mysql_show_dbs = _mysql.show_dbs()
        # print self.mysql_config
        #self.mysql_manager = mysql_mod.MYSQL_Manager()
        self.mysql_manager = mysql_mod.MYSQL_Manager(**self.mysql_config)

        if self.mysql_manager.isconnect :
            self.message("ok", "connect %s@%s ok" % (self.mysql_config['user'], self.mysql_config['host']))
            self.button_mysql_connect.setText(u"断开连接")
            self.mysql_show = mysql_mod.MYSQL_shows(self.mysql_manager)
            __dbs = self.mysql_show.show_databases()
            #清除comboBox内容，设置comboBox一个空的I
            self.comboBox_database.clear()
            self.comboBox_database.addItem("")
            if __dbs is not None:
                for db in __dbs:
                    self.comboBox_database.addItems(db)
                self.__Changed_connect()
        else:
            self.message("sqlError", self.mysql_manager._dbError)

    def __mysql_table(self):

        # 获取选中的数据库名称
        __db = self.comboBox_database.currentText()
        if self.comboBox_database.currentText() == "":
            self.listWidget_tables.clear()
        else:
            __tables = self.mysql_show.show_tables(__db)
            self.listWidget_tables.clear()
            if __tables is not None:
                for table in __tables:
                    self.listWidget_tables.addItems(table)
        # 清空数据表格
        self.tableWidget_tab_values.clear()
        self.tableWidget_tab_values.setRowCount(0)
        self.tableWidget_tab_values.setColumnCount(0)
        # 同步激活导入导出按钮
        self.button_mysql_open.setEnabled(True)
        self.button_mysql_input.setEnabled(True)
        self.button_mysql_output.setEnabled(True)
    # def __mysql_table_io(self):
    #     # _io 导入导出界面
    #     # 获取选中的数据库名称
    #     __db = self.comboBox_database_io.currentText()
    #     if self.comboBox_database_io.currentText() == "":
    #         self.listWidget_tables_io.clear()
    #     else:
    #         __tables = self.mysql_show.show_tables(__db)
    #         self.listWidget_tables_io.clear()
    #         if __tables is not None:
    #             for table in __tables:
    #                 self.listWidget_tables_io.addItems(table)

        # #_mysql = mysql_core.mysql_exec(self.mysql)
        # # 获取选中的数据库名称
        # mysql_db = self.comboBox_database_io.currentText()
        # sql_use = "use %s" % str(mysql_db)
        # # db_I = self.comboBox_database.currentIndex()
        # if self.comboBox_database_io.currentText() == "":
        #     self.listWidget_tables_io.clear()
        # else:
        #     mysql_2_1 = mysql_core.mysql_exec(self.mysql)
        #     mysql_2_1.sql_noresult(sql_use)
        #     _mysql_show_table = mysql_2_1.show_table()
        #     self.listWidget_tables_io.clear()
        #     if _mysql_show_table == -1:
        #         pass
        #     else:
        #         for r in mysql_2_1.tables:
        #             # print type(r)
        #             self.listWidget_tables_io.addItems(r)

    def __mysql_value(self):
        row = self.listWidget_tables.currentRow()

        index = self.__tab_index()
        if index == 1:
            self.tabWidget.removeTab(0)
        self.tabWidget.setCurrentIndex(1)

        if row != -1:
            table_name = self.listWidget_tables.item(row).text()

            __tab_values = self.mysql_show.show_tab_values(table_name)
            __tab_fileds =self.mysql_show.show_tab_fields(table_name)
            #_m_mysql_value_get = mysql_3.mysql_value_get()
            # self.textBrowser.clear()
            if __tab_fileds >0 :
                # 获取字段数量,列数
                columns = len(__tab_fileds)
                # 获取结果数量,行数
                # 如果结果为空，行数强制转为1
                if __tab_values >0 :
                    rows = len(__tab_values)
                    # 设置行数,列数
                    self.tableWidget_tab_values.setRowCount(rows)
                    self.tableWidget_tab_values.setColumnCount(columns)
                    # 先循环取出每行的结果，在循环填写每个值到对应的字段下面
                    row = -1
                    for r in __tab_values:
                        row += 1
                        col = -1
                        for v in r:
                            col += 1
                            #数据库默认编码，latin1
                            if type(v) == str:
                                tab_value = bytes(v)#str(v).decode("utf-8")
                            else:
                                tab_value = str(v).decode("utf-8")

                            # new 一个表格Item
                            newitem = QtGui.QTableWidgetItem()
                            # 给一个Item设置值
                            newitem.setText(tab_value)
                            # 把Item 设置到表格指定位置,row=行,cal=列
                            self.tableWidget_tab_values.setItem(row, col, newitem)

                else:
                    # 设置行数2,列数
                    self.tableWidget_tab_values.setRowCount(1)
                    self.tableWidget_tab_values.setColumnCount(columns)

                # 把字段循环取出来，添加到一个元组里面
                fields = []
                for fs in __tab_fileds:
                    fields.append(fs[0])
                    fields = QtCore.QStringList(fields)
                # 设置字段名称
                self.tableWidget_tab_values.setHorizontalHeaderLabels(fields)
                self.tableWidget_tab_values.resizeColumnsToContents()
                self.tableWidget_tab_values.horizontalHeader().setStretchLastSection(True)

    def __mysql_result(self,sql):
        self.tableWidget_result.clear()
        rowcount, __values,__fileds = self.mysql_manager.custom_sql_exec(sql)
        #_m_mysql_value_get = mysql_3.mysql_value_get()
        # self.textBrowser.clear()
        if __fileds > 0 :
            self.tabWidget.insertTab(0, self.tab_result, u"结果")
            # 获取字段数量,列数
            columns = len(__fileds)
            # 设置行数,列数
            self.tableWidget_result.setRowCount(rowcount)
            self.tableWidget_result.setColumnCount(columns)
            # 先循环取出每行的结果，在循环填写每个值到对应的字段下面
            row = -1
            for r in __values:
                row += 1
                col = -1
                for v in r:
                    col += 1
                    #数据库默认编码，latin1
                    tab_value = str(v).decode("utf-8")

                    # new 一个表格Item
                    newitem = QtGui.QTableWidgetItem()
                    # 给一个Item设置值
                    newitem.setText(tab_value)
                    # 把Item 设置到表格指定位置,row=行,cal=列
                    self.tableWidget_result.setItem(row, col, newitem)

            # 把字段循环取出来，添加到一个元组里面
            fields = []
            for fs in __fileds:
                fields.append(fs[0])
                fields = QtCore.QStringList(fields)
            # 设置字段名称
            self.tableWidget_result.setHorizontalHeaderLabels(fields)
        else:
            if str(self.tabWidget.tabText(0)) == "结果" :
                self.tabWidget.removeTab(0)

    def __mysql_exec(self):
        #print self.textEdit_exec.toPlainText()
        __sql_exec = str(self.textEdit_exec.toPlainText())
        self.__mysql_result(__sql_exec)

        text = self.mysql_manager.exec_msgs
        self.textEdit_msg.setText(text)
        #index = self.__tab_index()
        self.tabWidget.setCurrentIndex(0)
        #print text

    def __tab_index(self):
        if str(self.tabWidget.tabText(0)) == "结果":
            index = 1
        else:
            index = 0
        return index



    def __get_mysql_set(self):
        g_mysql_host = self.lineEdit_mysql_ip.text()
        g_mysql_port = self.lineEdit_mysql_port.text()
        if g_mysql_port == "":
            g_mysql_port = "0"
        g_mysql_user = self.lineEdit_mysql_user.text()
        g_mysql_passwd = self.lineEdit_mysql_passwd.text()
        g_mysql_db_name = self.comboBox_database.currentText()
        self.mysql_arg = self.lineEdit_mysql_arg.text()
        # 数据库配置组成一个字典作为参数, 两个**开头表示传递字典参数
        self.mysql_config = {
            'host': str(g_mysql_host),
            'port': int(g_mysql_port),
            'user': str(g_mysql_user),
            'passwd': str(g_mysql_passwd),
            'db': str(g_mysql_db_name),
            'charset': 'utf8'
        }
        # ss = self.mysql_ip+"\n"+self.mysql_port+"\n"+self.mysql_user+"\n"+self.mysql_passwd
        # self.textBrowser.setText(ss)
        # textBrowser 追加内容
        # self.textEdit_exec.append(str(r))

    def __file_path(self,typ):
        if typ == "save":
            file_name = QtGui.QFileDialog.getSaveFileName(self, "", "",
                                                      "SQL files(*.sql)")
        elif typ == "open":
            file_name = QtGui.QFileDialog.getOpenFileName(self, "open file dialog", "C:\Users\Administrator\Desktop",
                                                          "SQL files(*.*)")
        else:
            pass
        file_name = unicode(file_name)
        self.lineEdit_mysql_path.setText(file_name)
        del file_name

    def __mysql_dump(self):
        self.__file_path("save")
        file_name = str(self.lineEdit_mysql_path.text())
        _m_db = str(self.comboBox_database.currentText())
        if file_name == "":
            msg = u"请选择路径!"
            self.message("error", msg)
        elif _m_db == "":
            msg = u"请连接，并选择数据库!"
            self.message("error", msg)
        else:
            self.message("info", u"开始备份！")
            # 修改字典 重新给数据库参数赋值
            __sql_dump = mysql_mod.MysqlDump(self.mysql_manager,_m_db,file_name)
            __sql_dump.dump_exec()
            _iswrite, _write_msg = __sql_dump.get_iswrite_data()
            if _iswrite:
                self.message("ok", _write_msg)
            else:
                self.message("error", _write_msg)
            del __sql_dump

    def message(self,tille, message):

        QtGui.QMessageBox.information(self, tille, message)


if __name__ == "__main__":
    app = QtGui.QApplication(sys.argv)
    mysql_w = mysql_tool()
    mysql_w.show()
    sys.exit(app.exec_())
